﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace Garlic.DAL
{
    public class StVoyagesDAL
    {
        public const string ST_VOYAGES_SELECT_FOR_TABLE =
            @"select c.cst_cstm,
                    a.voy_iefg,
                    d.vsl_enname,
                    d.vsl_cnname,
                    a.voy_voyage,
                    e.sln_name,
                    g.cst_cstmcd, 
                    h.cst_cstmcd as vcm_cstmcd,
                    g.cst_cstm  as cst_agt_cstm,
                    h.cst_cstm  as cst_vcm_cstm,
                    voy_ptonnage,
                    voy_ltonnage,
                    a.VOY_SLN_ID,
                    a.voy_id,
                    a.voy_scd_id,
                    a.voy_intrade,
                    f.flt_schdul,
                    a.voy_flt_id
            from st_voyages a inner join
                st_schedules b on b.scd_id = a.voy_scd_id left join     
                st_customers c on c.cst_id = a.voy_scd_id left join
                st_vessels d on d.vsl_id = b.scd_vsl_id left join
                st_service_lines e on e.sln_id = a.voy_sln_id left join
                st_customers g on g.cst_id = e.sln_cst_agt_id left join 
                st_customers h on h.cst_id = e.sln_cst_vcm_id left join
                st_flights f on f.flt_id= a.voy_flt_id {0} ";

        public const string ST_VOYAGES_SELECT =
           @"SELECT 
			VOY_ID,
			VOY_SCD_ID,
			VOY_SLN_ID,
			VOY_FLT_ID,
			VOY_IEFG,
			VOY_VOYAGE,
			VOY_PTONNAGE,
			VOY_INTRADE,
			VOY_PARTITIONNM,
			VOY_ARCHIVETM,
			VOY_INSERTDT,
			VOY_INSERTUSER,
			VOY_LSTUPDDT,
			VOY_LSTUPUSER,
			VOY_LTONNAGE
			FROM ST_VOYAGES {0} ";

        /// <summary>
        /// GetTable
        /// </summary>
        /// <param name="textWhere">conditon</param>
        /// <param name="orderText">sort</param>
        /// <returns></returns>
        public DataTable GetTable(string textWhere, string orderText)
        {
            string query = string.Empty;
            if (!string.IsNullOrEmpty(textWhere))
                query += " where " + textWhere;
            if (!string.IsNullOrEmpty(orderText))
                query += " order by " + orderText;

            return GetDataTable(string.Format(ST_VOYAGES_SELECT_FOR_TABLE, query));
        }

        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="sql">SQL statement</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            DataTable dt = db.ExecuteDataTable(dbCommand);
            if (dt != null)
            {
                dt.TableName = "ST_VOYAGES";
            }
            return dt;
        }

        /// <summary>
        /// Get data collection
        /// </summary>
        /// <param name="textWhere">conditon</param>
        /// <param name="orderText">sort</param>
        /// <returns>StVoyagesInfo collection </returns>
        public DataTable GetDataTableBy_SCD_ID(string textWhere, string orderText)
        {
            string query = string.Empty;
            if (!string.IsNullOrEmpty(textWhere))
                query += " where " + textWhere;
            if (!string.IsNullOrEmpty(orderText))
                query += " order by " + orderText;

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(ST_VOYAGES_SELECT, query));
            return db.ExecuteDataTable(dbCommand);
        }

    }//end class
}
